﻿#include "OiPCH.hpp"
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlDriver>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>

namespace Oi
{
    namespace PLScannerCore
    {
        class DatabaseImpl : public Impl<Database>
        {
        public:
            void init();
            void exit();

        public:
            QSqlDatabase _db;
            QString _tb;
            QString _tb_fixed;
            QDateTime _lastTime;
        };

        void DatabaseImpl::init()
        {
            _tb = "plscanner_data";

            _tb_fixed = "plscanner_fixed_data";

            _db = QSqlDatabase::addDatabase("QSQLITE");

//             _db.setHostName("127.0.0.1");
//             _db.setDatabaseName("local_mysql");
//             _db.setUserName("thicknesslocal");
//             _db.setPassword("ch123456");
            QSqlQuery query(_db);
            _db.setDatabaseName(PLScanner::instance()->localDataDir);

            _lastTime = QDateTime::currentDateTime();//系统当前时间
            QDateTime aweekAgo = _lastTime.addMonths(-2);//参数是可以为负数的，负数就是获取以前的时间，正数就是获取未来的时间

            QString deleteSql = QString("delete from '%1' where datetime < '%2'").arg(_tb).arg(aweekAgo.toString("yyyy-MM-dd hh:mm:ss"));
            query.exec(deleteSql);
        }
        
        void DatabaseImpl::exit()
        {
            _db.close();
        }

        Database::Database()
        {
            OI_I(Database)->init();
        }

        Database::~Database()
        {
            OI_E(Database)->exit();
        }

        QString Database::tableName() const
        {
            return OI_IMPL()->_tb;
        }
        QSqlDatabase Database::sqlDatabase() const
        {
            return OI_IMPL()->_db;
        }
        QString Database::fixedTableName() const
        {
            return OI_IMPL()->_tb_fixed;
        }

        bool Database::open()
        {
            OI_F(Database);
            bool ret = d->_db.open();
            if (ret)
            {
                QSqlQuery query(d->_db);
                QString sql = QString("select count(*) from sqlite_master where type = 'table' and name='%1'").arg(d->_tb);

                query.exec(sql);

                if(query.next() && query.value(0).toInt() <= 0)
                {
                    sql = QString("CREATE TABLE %1 (id INTEGER PRIMARY KEY AUTOINCREMENT,"
                        "result INTEGER,"
                        "datetime TEXT NOT NULL,"
                        "formula TEXT,"
                        "user TEXT,"
                        "team INTEGER,"
                        "总宽 REAL,"
                        "总宽EL REAL,"
                        "边厚T1 REAL,"
                        "中厚T2 REAL,"
                        "中厚T3 REAL,"
                        "边厚T4 REAL,"
                        "宽度公差 REAL,"
                        "宽度标准值 REAL,"
<<<<<<< Updated upstream
                        "边厚T1公差 REAL,"
                        "边厚T1标准值 REAL,"
                        "中厚T2公差 REAL,"
                        "中厚T2标准值 REAL,"
                        "中厚T3公差 REAL,"
                        "中厚T3标准值 REAL,"
                        "边厚T4公差 REAL,"
                        "边厚T4标准值 REAL,"
                        "边厚T1差异值 REAL,"
                        "中厚T2差异值 REAL,"
                        "中厚T3差异值 REAL,"
                        "边厚T4差异值 REAL,"
                        "边厚差异 REAL)"
                       ).arg(d->_tb);
=======
                        "T1公差 REAL,"
                        "T1标准值 REAL,"
                        "T2公差 REAL,"
                        "T2标准值 REAL,"
                        "T3公差 REAL,"
                        "T3标准值 REAL,"
                        "T4公差 REAL,"
                        "T4标准值 REAL,"
                        "T5公差 REAL,"
                        "T5标准值 REAL,"
                        "T6公差 REAL,"
                        "T6标准值 REAL,"
                        "T7公差 REAL,"
                        "T7标准值 REAL,"
                        "T8公差 REAL,"
                        "T8标准值 REAL,"
                        "T9公差 REAL,"
                        "T9标准值 REAL,"
                        "T10公差 REAL,"
                        "T10标准值 REAL,"
                        "T1差异值 REAL,"
                        "T2差异值 REAL,"
                        "T3差异值 REAL,"
                        "T4差异值 REAL,"
                        "T5差异值 REAL,"
                        "T6差异值 REAL,"
                        "T7差异值 REAL,"
                        "T8差异值 REAL,"
                        "T9差异值 REAL,"
                        "T10差异值 REAL)"
                    ).arg(d->_tb);
>>>>>>> Stashed changes

                    query.prepare(sql);
                    if (!query.exec())
                    {
                        OiWarning() << query.lastError();
                        OiWarning() << sql;
                        ret = false;
                    }
                    else
                    {
                        ret = true;
                    }
                }
                else
                {
                    ret = true;
                }
            }
            else
            {
                OiWarning() << d->_db.lastError();
            }

            if (ret)
            {
                QSqlQuery query(d->_db);
                QString sql = QString("CREATE INDEX  DateIndex1 on %1 (datetime, formula)").arg(d->_tb);
                query.exec(sql);
            }

            //if (ret)
            //{
            //    QSqlQuery query(d->_db);
            //    QString sql = QString("select count(*) from sqlite_master where type = 'table' and name='%1'").arg(d->_tb_fixed);
            //    query.exec(sql);

            //    if (query.next() && query.value(0).toInt() <= 0)
            //    {
            //        sql = QString("CREATE TABLE %1 (id INTEGER PRIMARY KEY AUTOINCREMENT,"
            //            "result INTEGER,"
            //            "datetime TEXT NOT NULL,"
            //            "formula TEXT,"
            //            "user TEXT,"
            //            "team INTEGER,"
            //            "厚度标准值 REAL,"
            //            "厚度公差 REAL,"
            //            "厚度 REAL)"
            //        ).arg(d->_tb_fixed);

            //        query.prepare(sql);
            //        if (!query.exec())
            //        {
            //            OiWarning() << query.lastError();
            //            OiWarning() << sql;
            //            ret = false;
            //        }
            //        else
            //        {
            //            ret = true;
            //        }
            //    }
            //    else
            //    {
            //        ret = true;
            //    }
            //}

            //if (ret)
            //{
            //    QSqlQuery query(d->_tb_fixed);
            //    QString sql = QString("CREATE INDEX  DateIndex2 on %1 (datetime, formula)").arg(d->_tb_fixed);
            //    query.exec(sql);
            //}

            return ret;
        }

        bool Database::query(const QStringList keys, QMap<QString, QList<QPointF>>& data, const QDateTime from, const QDateTime end)
        {
            OI_F(Database);
            if (!d->_db.open())
            {
                return false;
            }

            QSqlQuery query(d->_db);
            query.prepare(QString("SELECT %1 FROM %2 WHERE datetime BETWEEN '%3' AND '%4'")
                .arg("datetime," + keys.join(','))
                .arg(d->_tb)
                .arg(from.toString("yyyy-MM-dd hh:mm:ss"))
                .arg(end.toString("yyyy-MM-dd hh:mm:ss")));

            query.exec();
            while (query.next())
            {
                qreal datetime = query.value(0).toDateTime().toMSecsSinceEpoch();

                int index = 1;
                for (auto& key : keys)
                {
                    data[key].append(QPointF(datetime, query.value(index).toDouble()));

                    index++;
                }
            }

            return true;
        }

        bool Database::queryFixedValues(const QStringList keys, QMap<QString, QList<QPointF>>& data, const QDateTime from, const QDateTime end)
        {
            OI_F(Database);
            if (!d->_db.open())
            {
                return false;
            }

            QSqlQuery query(d->_db);
            query.prepare(QString("SELECT %1 FROM %2 WHERE datetime BETWEEN '%3' AND '%4'")
                .arg("datetime," + keys.join(','))
                .arg(d->_tb_fixed)
                .arg(from.toString("yyyy-MM-dd hh:mm:ss"))
                .arg(end.toString("yyyy-MM-dd hh:mm:ss")));

            query.exec();
            while (query.next())
            {
                qreal datetime = query.value(0).toDateTime().toMSecsSinceEpoch();

                int index = 1;
                for (auto& key : keys)
                {
                    data[key].append(QPointF(datetime, query.value(index).toDouble()));

                    index++;
                }
            }

            return true;
        }

        QStringList Database::group(const QString key, const QString db)
        {
            OI_F(Database);

            QStringList ret;

            if(!d->_db.open())
            {
                return ret;
            }

            QSqlQuery query(d->_db);
            query.prepare(QString("SELECT %1 FROM %2 GROUP BY %1")
                .arg(key)
                .arg(db));

            query.exec();

            while(query.next())
            {
                auto value = query.value(0).toString();

                if(!value.isEmpty())
                {
                    ret.push_back(query.value(0).toString());
                }
            }
            return ret;
        }

        bool Database::insert(const DataRow& data)
        {
            OI_F(Database);
            if(!d->_db.open())
            {
                return false;
            }

//             QByteArray profile;
// 
//             QDataStream ps(&profile, QIODevice::WriteOnly);
// 
//             ps << data.profile;

            QSqlQuery query(d->_db);

            QString queryString = QString("INSERT INTO %1 (result, datetime, formula, user, team, 总宽, 总宽EL, 边厚T1, 中厚T2, 中厚T3, 边厚T4, 宽度公差, 宽度标准值, 边厚T1公差, 边厚T1标准值, 中厚T2公差, 中厚T2标准值, 中厚T3公差, 中厚T3标准值, 边厚T4公差, 边厚T4标准值,边厚T1差异值,中厚T2差异值,中厚T3差异值,边厚T4差异值,边厚差异) "
                "VALUES(%2, '%3', '%4', '%5', %6, %7, %8, %9, %10, %11, %12, %13, %14, %15, %16, %17, %18, %19, %20, %21, %22, %23, %24, %25, %26 ,%27)")
                .arg(d->_tb)
                .arg(data.result)
                .arg(data.datetime.toString("yyyy-MM-dd hh:mm:ss"))
                .arg(data.formula)
                .arg(data.user)
                .arg(data.team)
                .arg(data.width)
                .arg(data.width1)
                .arg(data.thickness1)
                .arg(data.thickness2)
                .arg(data.thickness3)
                .arg(data.thickness4)
                .arg(data.widthErr)
                .arg(data.widthStd)
                .arg(data.thickness1Err)
                .arg(data.thickness1Std)
                .arg(data.thickness2Err)
                .arg(data.thickness2Std)
                .arg(data.thickness3Err)
                .arg(data.thickness3Std)
                .arg(data.thickness4Err)
                .arg(data.thickness4Std)
                .arg(data.thickness1Dif)
                .arg(data.thickness2Dif)
                .arg(data.thickness3Dif)
                .arg(data.thickness4Dif)
                .arg(data.edgeThicknessDif);

            query.prepare(queryString);
           
            bool ret = query.exec();

            if (ret)
            {
//                 int id = 0;
// 
//                 query.exec(QString("select max(id) from %1").arg(d->_tb));
//                 if (query.next())
//                 {
//                     id = query.value(0).toInt();
//                 }
// 
//                 queryString = QString("INSERT INTO %1 (id, profile)"
//                     "VALUES(:id, :profile)")
//                     .arg(d->_tb_splicedata);
// 
//                 query.prepare(queryString);
//                 query.bindValue(":id", id);
//                 query.bindValue(":profile", profile);
// 
//                 ret = query.exec();
//                 if (!ret)
//                 {
//                     OiWarning()  << query.lastError();
//                     OiWarning() << queryString;
//                 }    
            }
            else
            {
                OiWarning() << query.lastError();
                OiWarning() << queryString;
            }
            QDateTime currentTime = QDateTime::currentDateTime();//系统当前时间
            currentTime = currentTime.addDays(-1);
            uint stime = currentTime.toTime_t();

            uint etime = d->_lastTime.toTime_t();
            int tRet = stime - etime;
            if (tRet > 0)
            {
                QDateTime aweekAgo = currentTime.addMonths(-2);//参数是可以为负数的，负数就是获取以前的时间，正数就是获取未来的时间
                QString deleteSql = QString("delete from '%1' where datetime < '%2'").arg(d->_tb).arg(aweekAgo.toString("yyyy-MM-dd hh:mm:ss"));
                query.exec(deleteSql);
                d->_lastTime = currentTime;
            }
            return ret;
        }

        bool Database::insert(const FixedValueRow& data)
        {
            OI_F(Database);
            if (!d->_db.open())
            {
                return false;
            }

            QSqlQuery query(d->_db);

            QString queryString = QString("INSERT INTO %1 (result, datetime, formula, user, team, 厚度, 厚度公差, 厚度标准值) "
                "VALUES(%2, '%3', '%4', '%5', %6, %7, %8, %9)")
                .arg(d->_tb_fixed)
                .arg(data.result)
                .arg(data.datetime.toString("yyyy-MM-dd hh:mm:ss"))
                .arg(data.formula)
                .arg(data.user)
                .arg(data.team)
                .arg(data.value)
                .arg(data.errValue)
                .arg(data.stdValue);

            query.prepare(queryString);

            bool ret = query.exec();

            if (ret)
            {

            }
            else
            {
                OiWarning() << query.lastError();
                OiWarning() << queryString;
            }

            return ret;
        }

        Database* Database::instance()
        {
            static Database s_database;
            return &s_database;
        }

    }
}